*===============================================================================
* Project title		Savings Accounts to Borrow Less	
* PIs			Felipe Kast, Dina Pomeranz 
* File name		2a_tables_analysis_borrowing.do
*-------------------------------------------------------------------------------
* Description		Creates borrowing tables, long- and short-term 
* Outputs		Tables 2, A4, A6
* Spaces per tab 	8 
*===============================================================================

*===============================================================================
*TOC
*1)	Prepare data for analysis
*2)	Tables
*2.1)	Borrowing table short term
*2.2)	Borrowing table long term  
*===============================================================================

*===============================================================================
* Section 1 - Prepare data for analysis
*===============================================================================

clear all
global path "~/file_server/project_savings/2_shared/impact/do/November2019"	// Path to processed data 
global results "$path/results"		
cd "$results"									// Change to directory to store results
set more off

*Loading processed data
	use $path/datasets/impactDatabase.dta, clear

*===============================================================================
* Section 2 - Tables 
*===============================================================================
* Section 2.1 - Table on Borrowing
*-------------------------------------------------------------------------------
* 2.1.1.Calculates table content ---------------------------------------------
*-------------------------------------------------------------------------------
//calculating content of panels C-F

//initializes counter	
	local i = 9								
	eststo clear

// iterate over Number-of-categories-outcome variables
foreach y in 												/// 
w5_shortTermBorrowing   w5_stBorrowFamily       w5_stBorrowProviders   w5_stBorrowInstitutions		/// 9 10 11 12
w1_shortTermBorrowing   w1_stBorrowFamily       w1_stBorrowProviders   w1_stBorrowInstitutions		/// 13 14 15 16
shortTermBorrowing      stBorrowFamily          stBorrowProviders      stBorrowInstitutions		/// 17 18 19 20
IHSshortTermBorrowing   IHSstBorrowFamily       IHSstBorrowProviders   IHSstBorrowInstitutions		/// 21 22 23 24
{
//trick to mark subjects nonmissing in both period	
	reg d.`y' 
	egen t1 = max(e(sample)), by(idBaseSurvey)
	
// Main regression 	
	
	reghdfe `y' c.accountAccess#c.post post  if t1 == 1, ///		// Regressing borrowing variables on treatment x post if non-missing in both periods  
	abs(idBaseSurvey ) vce(cluster groupId) 				// Individual FE, SE clustered at group level
	
//marking observations used in the regression 	
	gen `y'_s = e(sample) 
		
//adding control mean		
	sum `y' if accountAccess == 0 & post == 1
	if (abs(`r(mean)') < 100) {
		estadd local control_mean = trim("`: display %12.3fc r(mean)'")
	} 
	else {
		estadd local control_mean = trim("`: display %12.0fc r(mean)'")
	}
		
	drop t1
	distinct idBaseSurvey  if e(sample)					// Adding FE and N to estimates stored 
	estadd scalar num_ind        = r(ndistinct)
 	estadd local ind_fixeff      = "Yes"
	eststo A`i'
	local i = `i' + 1 //updates counter	
	
}

//create new e(sample) variables to add as restrictions in the next regression 	
gen prShortTermBorrowingTot_s = w5_shortTermBorrowing_s
gen stBorrowTotalIndex_s = w1_shortTermBorrowing_s
gen prShortTermBorrowFamily_s = w5_stBorrowFamily_s
gen stBorrowFamilyIndex_s = w1_stBorrowFamily_s
gen prShortTermBorrowProv_s = w5_stBorrowProviders_s
gen stBorrowProvidersIndex_s = w1_stBorrowProviders_s
gen prShortTermBorrowInst_s = w5_stBorrowInstitutions_s
gen stBorrowInstitutionsIndex_s = w1_stBorrowInstitutions_s

//calculating contents of panels A & B 

//initializes counter	
	local i = 1 								

// iterate over Number-of-categories-outcome variables
foreach y in 												///
prShortTermBorrowingTot prShortTermBorrowFamily prShortTermBorrowProv  prShortTermBorrowInst		/// 1 2 3 4 		
stBorrowTotalIndex      stBorrowFamilyIndex     stBorrowProvidersIndex stBorrowInstitutionsIndex	/// 5 6 7 8   
{
//trick to mark subjects nonmissing in both period	
	reg d.`y' 
	egen t1 = max(e(sample)), by(idBaseSurvey)		

// Main regression 
	reghdfe `y' c.accountAccess#c.post post  if t1 == 1 & `y'_s == 1, ///	// Regressing borrowing variables on treatment x post if non-missing in both periods, extra e(sample) restriction added here 
	abs(idBaseSurvey) vce(cluster groupId) 					// Individual FE, SE clustered at group level
		
//adding control mean		
	sum `y' if accountAccess == 0 & post == 1
	if (abs(`r(mean)') < 100) {
		estadd local control_mean = trim("`: display %12.3fc r(mean)'")
	} 
	else {
		estadd local control_mean = trim("`: display %12.0fc r(mean)'")
	}
		
	drop t1
	distinct idBaseSurvey  if e(sample)					// Adding FE and N to estimates stored
	estadd scalar num_ind        = r(ndistinct)
 	estadd local ind_fixeff      = "Yes"
	eststo A`i'
	local i = `i' + 1 //updates counter	
	
}
	
*-------------------------------------------------------------------------------
* 2.1.2 Writes table -----------------------------------------------------------
*-------------------------------------------------------------------------------
* Version 1 - 6 panels with additional transformations for the appendix 
//probability panel
	esttab A1 A2 A3 A4 using tab_borrow.tex, replace				/// 	
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post )							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead(\begin{tabular}{l*{@M}{c}}  \hline \hline 				///
	& (1) 		& (2) 	     & (3) 	    & (4) 	\\ 			///
	& Total		& Owed to    & Owed to   & Owed to	\\ 			///
	& short-term	& family and & service   & business 	\\ 			///
	& borrowing	& friends    & providers & contacts and \\ 			///
	& 		&    	     & 	         & institutions	\\) 			///
	posthead(\hline \\ \multicolumn{@span}{l}{\textbf{Panel A:} Probability of Any Borrowing} \\) ///
	prefoot( ) postfoot( )  	
//Categories panel
	esttab A5 A6 A7 A8 using tab_borrow.tex, append 				///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber 	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post )  							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel B:} Categories of Borrowing} \\ ) ///
	prefoot( ) postfoot( ) 
//winsor 5%
	esttab A9 A10 A11 A12 using tab_borrow.tex, append				///	
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean")) mlabels(,none)	 					///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post ) 	 						///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel C:} Amounts (Winsorized at Top 5\%)} \\ ) ///
	prefoot( ) postfoot( ) 
//winsor 1%
	esttab A13 A14 A15 A16 using tab_borrow.tex, append				///	
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean")) mlabels(,none)	 					///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post )	  						///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel D:} Amounts (Winsorized at Top 1\%)} \\ ) ///
	prefoot( ) postfoot( )
//non-winsorized 
	esttab A17 A18 A19 A20 using tab_borrow.tex, append				///	
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean")) mlabels(,none)	 					///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post )  							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel E:} Amounts (Non-Winsorized)} \\ ) ///
	prefoot( ) postfoot( )
//IHS
	esttab A21 A22 A23 A24 using tab_borrow.tex, append 						///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber 			///
	stats( control_mean num_ind N ind_fixeff, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean" "\\ \hline Individuals" "Observations" "Individual FE")) mlabels(,none)	///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post )  							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel F:} Inverse Hyperbolic Sine of Amount} \\ ) ///	
	prefoot( ) postfoot(\hline \hline \end{tabular})  
	
	
* Version 2 - 3 panels for the paper 
//probability panel
	esttab A1 A2 A3 A4 using tab_borrow_main.tex, replace				/// 	
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post )							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead(\begin{tabular}{l*{@M}{c}}  \hline \hline 				///   
	& (1) 		& (2) 	     & (3) 	    & (4) 	\\ 			///
	& Total		& Owed to    & Owed to   & Owed to	\\ 			///
	& short-term	& family and & service   & business 	\\ 			///
	& borrowing	& friends    & providers & contacts and \\ 			///
	& 		&    	     & 	         & institutions	\\) 			///
	posthead(\hline \\ \multicolumn{@span}{l}{\textbf{Panel A:} Probability of Any Borrowing} \\ ) ///
	prefoot( ) postfoot( )  	

//winsor 5%
	esttab A9 A10 A11 A12 using tab_borrow_main.tex, append				///	
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean")) mlabels(,none)	 					///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post ) 	 						///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel B:} Amounts (Winsorized at Top 5\%)} \\ ) ///
	prefoot( ) postfoot( ) 

//IHS
	esttab A21 A22 A23 A24 using tab_borrow_main.tex, append 					///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber 			///
	stats( control_mean num_ind N ind_fixeff, fmt(%12.0fc %12.0fc %12.0fc) 				///
	label("Control mean" "\\ \hline Individuals" "Observations" "Individual FE")) mlabels(,none)	///
	collabels(, none) eqlabels(, none) 						/// 
	keep(c.accountAccess#c.post )  							///
	varlabels(c.accountAccess#c.post "Account $\times$ post") 			///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( ) 									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel C:} Inverse Hyperbolic Sine of Amount} \\ ) ///	
	prefoot( ) postfoot(\hline \hline \end{tabular})  
	

*-------------------------------------------------------------------------------
*Section 2.1 - Table on Long Term Borrowing
*-------------------------------------------------------------------------------
* 2.1.1.Calculates table content -----------------------------------------------
*-------------------------------------------------------------------------------
	local i = 1
	foreach y in prlongTermBorrowing longTermBorrowingIndex IHSlongTermBorrowing ///
	{ //Col1, Col2, Col6 are created here 
	if "`y'" 		== "longTermBorrowingIndex" {
	local i 		= 2
	}
	else if "`y'"		== "IHSlongTermBorrowing" {
	local i 		= 6
	}
	reg d.`y'								// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local C`i'R1: di %12.4fc m[1,1]
	/*s.e.*/		local C`i'R2: di %12.4fc m[2,1]
				local C`i'R2: di trim("`C`i'R2'")
	/*(s.e.) */		local C`i'R2="(`C`i'R2')"
	/*p-val*/		local pv`i': di %12.4fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local C`i'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local C`i'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local C`i'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local C`i'R6 = "Yes"
	/*StratumXPost fe*/	local C`i'R7 = "Yes"
	drop t1
	}
	
	*-------
	//the other 3 Y vars
	*-------
	
	local i = 3
	*local y w5_longTermBorrowing
	foreach y in w5_longTermBorrowing w1_longTermBorrowing longTermBorrowing ///
	{ //Col 3, 4 5
	
	reg d.`y'								// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			///
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	/*coef */		local C`i'R1: di %12.0fc m[1,1]
	/*s.e.*/		local C`i'R2: di %12.0fc m[2,1]
				local C`i'R2: di trim("`C`i'R2'")
	/*(s.e.) */		local C`i'R2="(`C`i'R2')"
	/*p-val*/		local pv`i': di %12.3fc m[4,1]


	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local C`i'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local C`i'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // controls mean outcome
	/*controlmean*/		local C`i'R3: di %12.0fc `r(mean)'
	/*Individual fe*/	local C`i'R6 = "Yes"
	/*StratumXPost fe*/	local C`i'R7 = "Yes"
	drop t1
	
	local i = `i' + 1 // update counter
	}
	
	
*** Put stars in regression coefficients
	forvalue i=1/6 {
	*stars
	if `pv`i''<=0.10 & `pv`i''>0.05 {
	local C`i'R1="$`C`i'R1'^{*}$"
	}
	else if  `pv`i''<=0.05 &  `pv`i''>0.01 {
	local C`i'R1="$`C`i'R1'^{**}$"
	}
	else if  `pv`i''<=0.01 { 
	local C`i'R1="$`C`i'R1'^{***}$"
	}
	}
	
// Row titles

	local lbl_1 "Account $\times$ post"	
	local lbl_2 ""
	local lbl_3 "Control mean"
	local lbl_4 "Individuals"
	local lbl_5 "Observations"

	local lbl_6 "Individual FE"
	local lbl_7 "Stratum FE $\times$ post"

//Table
	texdoc init $results/tab_borrow_longterm.tex, replace force		// Note: Need to include tablurax environment in latex.	
	tex \\ \\ [-1.5ex]
	tex \hline\hline \\ [-1.5ex]
	tex 	& (1) 				& (2)			  & (3) 			& (4) 				 & (5)	 		    & (6)		\\
	tex {}	& Probability of any borrowing	& Categories of borrowing & Amounts (winsorized at 5\%) & Amounts (winsorized at 1\%) 	 & Amounts (non-winsorized) & IHS of amounts	\\ 
	tex \hline
	forvalue t=1/3 {
	tex `lbl_`t'' & `C1R`t'' 		& `C2R`t''	 & `C3R`t'' 	& `C4R`t'' 	& `C5R`t'' 	& `C6R`t'' 	 \\
	}
	tex  \hline 
	forvalue t=4/6 {
	tex `lbl_`t'' & `C1R`t'' & `C2R`t'' & `C3R`t'' & `C4R`t'' & `C5R`t'' & `C6R`t'' \\
	}
	tex  \hline\hline \\ [-1.5ex]
	texdoc close
	
